﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using WebBanThuoc.DTO;

namespace WebBanThuoc.DAO
{
    public class LoaiSanPham_DAO
    {
        private SqlConnection connection;
        //public bool hasData { set; get; }
        public LoaiSanPham_DAO(SqlConnection connection)
        {
            this.connection = connection;
        }

        private List<LoaiSanPham> getListBy(string whereCom, string orderCom)
        {
            List<LoaiSanPham> data = new List<LoaiSanPham>();
            string comStr = "Select * from [LoaiSanPham]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        LoaiSanPham obj = new LoaiSanPham();

                        obj.MaLoaiSanPham = dap.DaReader["MaLoaiSanPham"].ToString();
                        obj.TenLoaiSanPham = dap.DaReader["TenLoaiSanPham"].ToString();
                        obj.GhiChu = dap.DaReader["GhiChu"].ToString();
                        obj.MaDanhMuc = dap.DaReader["MaDanhMuc"].ToString();
                        obj.IsStatus = Convert.ToInt32(dap.DaReader["IsStatus"]);                        

                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public List<LoaiSanPham> getListByKey()
        {
            return getListBy("", " [MaLoaiSanPham] asc");
        }

        public string createMaNhaSanXuat()
        {
            string result = "";
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, "SELECT [dbo].[fnc_CreateNew_MaLoaiSanPham] () as col");
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    result = dap.DaReader["col"].ToString().Trim();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        private int delete(string whereCom)
        {
            string comUpdate = "DELETE FROM [LoaiSanPham] ";
            if (whereCom.Trim() != "")
                comUpdate += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(this.connection);
                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comUpdate);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int deleteByKey(string MaLoaiSanPham)
        {
            return delete(" [MaLoaiSanPham]='" + MaLoaiSanPham + "' ");
        }

        public int add(LoaiSanPham obj)
        {
            string comQuery = "INSERT INTO [LoaiSanPham] ([MaLoaiSanPham],[TenLoaiSanPham]"
               + ",[GhiChu],[MaDanhMuc],[IsStatus])"
               + " VALUES (@MaLoaiSanPham,@TenLoaiSanPham,@GhiChu"
               + ",@MaDanhMuc,@IsStatus)";

            try
            {
                IDataProvider dap = new DataProvider(connection);

                dap.AddParameters("@MaLoaiSanPham", obj.MaLoaiSanPham, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@TenLoaiSanPham", obj.TenLoaiSanPham, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@GhiChu", obj.GhiChu, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@MaDanhMuc", obj.MaDanhMuc, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@IsStatus", obj.IsStatus, System.Data.SqlDbType.Int);

                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        private int update(LoaiSanPham obj, string whereCom)
        {

            string comQuery = "UPDATE [LoaiSanPham] SET [MaLoaiSanPham]=@MaLoaiSanPham,[TenLoaiSanPham]=@TenLoaiSanPham"
               + ",[GhiChu]=@GhiChu,[MaDanhMuc]=@MaDanhMuc,[IsStatus]=@IsStatus ";
            if (whereCom.Trim() != "")
                comQuery += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.AddParameters("@MaLoaiSanPham", obj.MaLoaiSanPham, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@TenLoaiSanPham", obj.TenLoaiSanPham, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@GhiChu", obj.GhiChu, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@MaDanhMuc", obj.MaDanhMuc, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@IsStatus", obj.IsStatus, System.Data.SqlDbType.Int);

                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int updateByKey(LoaiSanPham data, string MaLoaiSanPham)
        {
            return update(data, " [MaLoaiSanPham]='" + MaLoaiSanPham + "' ");
        }

        private LoaiSanPham getBy(string whereCom, string orderCom)
        {
            LoaiSanPham data = null;
            string comStr = "Select * from [LoaiSanPham]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {

                    dap.DaReader.Read();
                    data = new LoaiSanPham();

                    data.MaLoaiSanPham = dap.DaReader["MaLoaiSanPham"].ToString();
                    data.TenLoaiSanPham = dap.DaReader["TenLoaiSanPham"].ToString();
                    data.GhiChu = dap.DaReader["GhiChu"].ToString();
                    data.MaDanhMuc = dap.DaReader["MaDanhMuc"].ToString();
                    data.IsStatus = Convert.ToInt32(dap.DaReader["IsStatus"]);

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public LoaiSanPham getByKey(string MaLoaiSanPham)
        {
            return getBy(" [MaLoaiSanPham]='" + MaLoaiSanPham + "' ", " [MaLoaiSanPham] asc");
        }

        public string getKeyByName(string tenLSP)
        {
            string maLSP = "";
            LoaiSanPham obj = null;
            try
            {
                IDataProvider dap = new DataProvider(connection);
                string comStr = "Select * from [LoaiSanPham] where [TenLoaiSanPham] like N'%" + tenLSP + "%'";
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                if (dap.HasData)
                {
                    obj = new LoaiSanPham();
                    dap.DaReader.Read();
                    obj.MaLoaiSanPham = dap.DaReader["MaLoaiSanPham"].ToString();
                    maLSP = obj.MaLoaiSanPham;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return maLSP;
        }

        public List<LoaiSanPham> getListByKey(string MaDanhMuc)
        {
            return getListBy(" [MaDanhMuc]='" + MaDanhMuc + "' ", " [MaLoaiSanPham] asc");
        }

    }
}